import pymysql
import pymysql.cursors
from config.config import db_config

class DBUtils:
    """
    数据库工具类，提供数据库连接和操作方法
    """
    
    def __init__(self):
        """
        初始化数据库工具类
        """
        self.connection = None
    
    def connect(self):
        """
        建立数据库连接
        :return: 数据库连接对象
        """
        if self.connection is None or not self.connection.open:
            try:
                self.connection = pymysql.connect(
                    host=db_config.DB_HOST,
                    port=db_config.DB_PORT,
                    user=db_config.DB_USER,
                    password=db_config.DB_PASSWORD,
                    database=db_config.DB_NAME,
                    cursorclass=pymysql.cursors.DictCursor
                )
            except Exception as e:
                raise Exception(f"数据库连接失败: {str(e)}")
        return self.connection

    # 参数化查询：通过 params 防止 SQL 注入
    def execute_query(self, sql, params=None):
        """
        执行SQL查询语句
        :param sql: SQL查询语句
        :param params: SQL参数（可选）
        :return: 查询结果列表
        """
        connection = self.connect()
        try:
            with connection.cursor() as cursor:
                cursor.execute(sql, params)
                result = cursor.fetchall()
                return result
        #     结果处理：返回所有查询结果（字典列表）
        except Exception as e:
            raise Exception(f"查询执行失败: {str(e)}")
    
    def execute_update(self, sql, params=None):
        """
        执行SQL更新语句（INSERT, UPDATE, DELETE等）
        :param sql: SQL更新语句
        :param params: SQL参数（可选）
        :return: 受影响的行数
        """
        connection = self.connect()
        try:
            with connection.cursor() as cursor:
                affected_rows = cursor.execute(sql, params)
                connection.commit()
                return affected_rows
        except Exception as e:
            connection.rollback()
            raise Exception(f"更新执行失败: {str(e)}")
    
    def execute_many(self, sql, params_list):
        """
        批量执行SQL语句
        :param sql: SQL语句
        :param params_list: 参数列表
        :return: 受影响的行数
        """
        connection = self.connect()
        try:
            with connection.cursor() as cursor:
                affected_rows = cursor.executemany(sql, params_list)
                connection.commit()
                return affected_rows
        except Exception as e:
            connection.rollback()
            raise Exception(f"批量执行失败: {str(e)}")
    
    def close(self):
        """
        关闭数据库连接
        """
        if self.connection and self.connection.open:
            self.connection.close()
            self.connection = None
    
    def __enter__(self):
        """\支持上下文管理器"""
        self.connect()
        return self
    
    def __exit__(self, exc_type, exc_val, exc_tb):
        """\支持上下文管理器"""
        self.close()

# 创建全局实例
db_utils = DBUtils()